﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Sockets;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Web.UI.WebControls;
using ConsoleApplication11.Models;
using Dapper;
using NPOI.SS.UserModel;
namespace ConsoleApplication11
{
    class Program
    {
        static void Main(string[] args)
        {
            var ad = SelectColumnCats();
            List<string> list = new List<string>();
            string[] title = {"列名","长度","类型","说明","是否为空"};
            foreach (var itme in ad)
            {
                //list.Add(itme.name);  
                IEnumerable<Column> dd = SelectColumn(itme.name);
               
                 Excel(title,itme.name,dd);       
            }

        }

        public static void Excel(string[] title, string name, IEnumerable<Column> c)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
            NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
            ICellStyle style = book.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            headerrow.CreateCell(1).SetCellValue(name);
            headerrow = sheet.CreateRow(1);

            headerrow.CreateCell(1).SetCellValue(name);
            int i = 0;
            foreach (var column in title)
            {
                headerrow.CreateCell(i).SetCellValue(column);
                i++;
            }
            int rowIndex = 1;

            foreach (var row in c)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (var column in c)
                {
                    //dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());

                }

                rowIndex++;
            }

            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode("213" + "" + DateTime.Now.ToString("yyyMMddHHmmss"), System.Text.Encoding.UTF8)));
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            //HttpContext.Current.Response.End();

            //HttpContext.Current.ApplicationInstance.CompleteRequest 
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            book = null;
            ms.Close();
            ms.Dispose();
        }
        public static void NpoiExcel(DataTable dt, string fileName)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");
            NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);
            ICellStyle style = book.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            foreach (DataColumn column in dt.Columns)
                headerrow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            int rowIndex = 1;

            foreach (DataRow row in dt.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }

            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(fileName + "" + DateTime.Now.ToString("yyyMMddHHmmss"), System.Text.Encoding.UTF8)));
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            //HttpContext.Current.Response.End();

            //HttpContext.Current.ApplicationInstance.CompleteRequest 
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            book = null;
            ms.Close();
            ms.Dispose();
        }

        public static IEnumerable<Table> SelectColumnCats()
        {
            using (IDbConnection conn = OpenConnection())
            {
                const string query = "SELECT name, id From sysobjects WHERE xtype = 'u' ORDER BY name ASC";
                return conn.Query<Table>(query, null);
            }
        }

        public static IEnumerable<Column> SelectColumn(string name)
        {
            using (IDbConnection conn = OpenConnection())
            {
                string query = @"SELECT syscolumns.name, syscolumns.length, systypes.name   as   typename, sys.extended_properties.[value] AS tname,syscolumns.isnullable  from   syscolumns join systypes   on     syscolumns.xtype=systypes.xtype   and   systypes.name <> 'sysname ' LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.minor_id = syscolumns.colid AND sys.extended_properties.major_id = syscolumns.id) where   syscolumns.id   in (select  id   from   sysobjects   where   name= @name)";
                var dd = conn.Query<Column>(query, new { name = name });
                return dd;
            }
            return null;
        }



        //获取Sql Server的连接数据库对象。SqlConnection
        public static SqlConnection OpenConnection()
        {
            AnZhen_DBContext db = new AnZhen_DBContext();
            var d = db.Database.Connection.ConnectionString;
            SqlConnection connection = new SqlConnection(d);
            connection.Open();
            return connection;
        }
        //获取MySql的连接数据库对象。MySqlConnection
        //public MySqlConnection OpenConnection()
        //{
        //     MySqlConnection connection = new MySqlConnection(mysqlconnectionString);
        //     connection.Open();
        //     return connection;
        //}
    }

    public class Table
    {
        public string name { get; set; }
    }

    public class Column
    {
        public string name { get; set; }
        public string length { get; set; }
        public string typename { get; set; }
        public string tname { get; set; }
        public string isnullable { get; set; }

    }
}
